package com.orange.modules;

import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.modules.bean.school_data.AuthMoneyExport;
import com.orange.modules.bean.school_data.AuthResourceExport;
import com.orange.modules.bean.school_data.AuthSchoolRunningExport;
import com.orange.modules.bean.school_data.AuthStudentExport;
import com.orange.modules.bean.school_data.AuthTeacherExport;
import com.orange.modules.bean.school_data.AuthTeacherResouceExport;
import com.orange.modules.dao.SchoolDataDao;
import com.orange.modules.entity.school_data.AuthInspectionCollect;
import com.orange.modules.entity.school_data.AuthItemData;

@Repository
public class SchoolDataRepository {
	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	private SchoolDataDao schoolDataDao;

	public List<AuthMoneyExport> queryAuthMoneyResouce(String major) throws SQLException{
		String sql = "SELECT"
			+ " s_training_funds AS trainingFunds,"
			+ " s_professional_training_funds AS professionalTrainingFunds,"
			+ " s_budget_standards as budgetStandards,"
			+ " s_were_born as wereBorn,"
			+ " s_average_education_funding_ratio AS averageEducationFundingRatio,"
			+ " s_outlay_per_student AS outlayPerStudent,"
			+ " s_equipment_investment AS equipmentInvestment,"
			+ " s_professional_library_funding AS professionalLibraryFunding,"
			+ " s_funding_education_funding_per AS fundingEducationFundingPer,"
			+ " login_type AS logintype,"
			+ " s_school_name AS sname"
			+ " from t_auth_money_resource as a INNER JOIN t_auth_school as t on a.s_id = t.p_id"
			+ " where t.s_major=?";
		List<AuthMoneyExport> as = dbHelper.getBeanList(sql, AuthMoneyExport.class, major);
		return as;
	}
	
	public List<AuthResourceExport> queryAuthResouceData(String major) throws SQLException{
		String sql = "SELECT"
			+" s_total_library_collection AS totalLibraryCollection,"
			+" s_electronic_books AS electronicBooks,"
			+" s_related_professional_books as relatedProfessionalBooks,"
			+" s_student_related_professional_books as studentRelatedProfessionalBooks,"
			+" s_recommended_book AS recommendedBook,"
			+" s_average_annual_book AS averageAnnualBook,"
			+" s_readingroom_journals AS readingroomJournals,"
			+" s_students_free_reading AS studentsFreeReading,"
			+" s_students_digital_resources AS studentsDigitalResources,"
			+" s_professional_journals AS professionalJournals,"
		    +" s_skills_training_room_area AS skillsTrainingRoomArea,"
		    +" s_shared_area AS sharedArea,"
		    +" s_skills_training_room_types AS skillsTrainingRoomTypes,"
		    +" s_shared_room AS sharedRoom,"
		    +" s_equipment_cost AS equipmentCost,"
		    +" s_shared_device_value AS sharedDeviceValue,"
		    +" s_school_name AS sname"
		    +" from t_auth_resource_data as a LEFT JOIN t_auth_school as t on a.s_id = t.p_id"
		    +" where t.s_major = ?";
		List<AuthResourceExport> as = dbHelper.getBeanList(sql, AuthResourceExport.class, major);
		return as;
	}
	
	public List<AuthSchoolRunningExport> queryAuthSchoolRunning(String major) throws SQLException{
		String sql = "SELECT"
			+" s_school_start_time AS schoolStartTime,"
			+" s_reorganization_time AS reorganizationTime,"
			+" s_school_upgraded_college_time as schoolUpgradedCollegeTime,"
			+" s_undergraduate_school_time as undergraduateSchoolTime,"
			+" s_undergraduate_professional_start_time AS undergraduateProfessionalStartTime,"
			+" s_specialty_startup_time AS specialtyStartupTime,"
			+" login_type AS loginType,"
			+" s_school_name AS sname"
			+" from t_auth_school_running_data as a LEFT JOIN t_auth_school as t on a.s_id = t.p_id"
			+" where t.s_major = ?";
		List<AuthSchoolRunningExport> as = dbHelper.getBeanList(sql, AuthSchoolRunningExport.class, major);
		return as;
	}
	
	public List<AuthStudentExport> queryAuthStudentData(String major) throws SQLException{
		String sql = "SELECT"
			+" s_graduated_students AS graduatedStudents,"
			+" s_graduated_undergraduate_major AS graduatedUndergraduateMajor,"
			+" s_graduated_specialized_subject as graduatedSpecializedSubject,"
			+" s_school_all_students as schoolAllStudents,"
			+" s_all_undergraduate_major AS allUndergraduateMajor,"
			+" s_all_specialized_subject AS allSpecializedSubject,"
			+" s_inschool_students AS inschoolStudents,"
			+" s_inschool_undergraduate_major AS inschoolUndergraduateMajor,"
			+" s_inschool_specialized_subject AS inschoolSpecializedSubject,"
			+" s_inschool_boy AS inschoolboy,"
			+" s_inschool_girl AS inschoolgirl,"
			+" s_teacher_qualification_certificate AS teacherQualificationCertificate,"
			+" s_relevant_educational_institutions AS relevantEducationalInstitutions,"
			+" s_employing_units AS employingUnits,"
			+" login_type AS logintype,"
			+" s_school_name AS sname"
			+" from t_auth_student_data as a LEFT JOIN t_auth_school as t on a.s_id = t.p_id"
			+" where t.s_major = ?";
		List<AuthStudentExport> as = dbHelper.getBeanList(sql, AuthStudentExport.class, major);
		return as;
	}
	
	public List<AuthTeacherExport> queryAuthTeacherData(String major) throws SQLException{
		String sql = "SELECT"
			+" s_fulltime_teacher_num AS fulltimeTeacherNum,"
			+" s_school_series_num AS schoolSeriesNum,"
			+" s_school_privatdozent_num as schoolPrivatdozentNum,"
			+" s_professional_fulltime_teacher_num as professionalFulltimeTeacherNum,"
			+" s_professional_series_num AS professionalSeriesNum,"
			+" s_professional_privatdozent_num AS professionalPrivatdozentNum,"
			+" s_higher_education_fulltime_teacher_num AS higherEducationFulltimeTeacherNum,"
			+" s_doctoral_teachers_num AS doctoralTeachersNum,"
			+" s_master_teachers_num AS masterTeachersNum,"
			+" s_master_ratio AS masterRatio,"
			+" s_high_professional_education_fulltime_teacher_num AS highProfessionalEducationFulltimeTeacherNum,"
			+" s_doctoral_degrees_num AS doctoralDegreeNum,"
			+" s_reading_doctoral_num AS readingDoctoralNum,"
			+" s_master_degree_num AS masterDegreeNum,"
			+" s_reading_master_num AS readingMasterNum,"
			+" s_senior_fulltime_teacher_num AS seniorFulltimeTeacherNum,"
			+" s_school_professor_num AS schoolProfessorNum,"
			+" s_school_associate_professor_num AS schoolAssociateProfessorNum,"
			+" s_professional_higher_fulltime_teacher_num AS professionalHigherFulltimeTeacherNum,"
			+" s_professional_professor_num AS professionalProfessorNum,"
			+" s_professional_associate_professor_num AS professionalAssociateProfessorNum,"
			+" s_professional_lecturer_num AS professionalLecturerNum,"
			+" s_oneyear_kindergarten_teachers_num AS oneyearKindergartenTeachersNum,"
			+" s_noyear_kindergarten_teachers_num AS noyearKindergartenTeachersNum,"
			+" s_excellent_teachers_num AS excellentTeachersNum,"
			+" login_type AS logintype,"
			+" s_school_name AS sname"
			+" from t_auth_teacher_data as a LEFT JOIN t_auth_school as t on a.s_id = t.p_id"
			+" where t.s_major = ?";
		List<AuthTeacherExport> as = dbHelper.getBeanList(sql, AuthTeacherExport.class, major);
		return as;
	}
	
	public List<AuthTeacherResouceExport> queryAuthTeacherResource(String major) throws SQLException{
		String sql = "SELECT"
			+" s_latest_professional_construction_time AS latestProfessionalConstructionTime,"
			+" s_professional_training_program AS professionalTrainingProgram,"
			+" s_required_courses_num as requiredCoursesNum,"
			+" s_elective_courses_num as electiveCoursesNum,"
			+" s_online_course_time AS onlineCourseTime,"
			+" s_online_assessment_time AS onlineAssessmentTime,"
			+" s_teaching_cycle AS teachingCycle,"
			+" s_teaching_satisfaction AS teachingSatisfaction,"
			+" s_credit_system_time AS creditSystemTime,"
			+" s_basic_course_credit AS basicCourseCredit,"
			+" s_professional_course_credits AS professionalCourseCredits,"
			+" s_curriculum_credits AS curriculumCredits,"
			+" s_county_num AS countyNum,"
			+" s_practice_base AS practiceBase,"
			+" s_education_practice_base AS educationPracticeBase,"
			+" s_teaching_practice_time AS teachingPracticeTime,"
			+" s_educational_probation AS educationalProbation,"
			+" s_educational_practice AS educationalPractice,"
			+" s_educational_practice_time AS educationalPracticeTime,"
			+" s_base_instructor AS baseInstructor,"
			+" s_ratio_teacher_student AS ratioTeacherStudent,"
			+" s_fulltime_teachers_latest AS fulltimeTeachersLatest,"
			+" s_parttime_teachers_latest AS parttimeTeachersLatest,"
			+" login_type AS logintype,"
			+" s_school_name AS sname"
			+" from t_auth_teach_resource as a LEFT JOIN t_auth_school as t on a.s_id = t.p_id"
			+" where t.s_major = ?";
		List<AuthTeacherResouceExport> as = dbHelper.getBeanList(sql, AuthTeacherResouceExport.class, major);
		return as;
	}
	/**
	 * 材料评审结果汇总
	 * @param major
	 * @return
	 * @throws SQLException
	 */
	public List<AuthItemData> queryAuthExportItemData(String major) throws SQLException{
		String sql = "SELECT "
			+" s_school_name AS schoolName, "
			+" s_item_id AS itemId,"
			+" s_expert_name AS expertName, "
			+" g.s_name AS name, "
			+" t.s_type AS type, "
			+" a.s_major AS major,"
			+" a.s_content AS content, "
			+" t.s_review as review "
			+" FROM "
			+" t_auth_item_expert_data as t LEFT JOIN t_auth_school as s ON t.s_school_id=s.p_id "
			+" LEFT JOIN t_auth_item  as a ON t.s_item_id=a.p_id "
			+" LEFT JOIN t_auth_group as g on t.s_group_id=g.p_id "
			+" LEFT JOIN t_auth_expert AS e ON t.s_expert_id=e.p_id"
			+" WHERE "
			+" a.s_major=?";
		List<AuthItemData> as = dbHelper.getBeanList(sql, AuthItemData.class, major);
		return as;
	}
	
	/**
	 * 现场考察结果汇总
	 * @param major
	 * @return
	 * @throws SQLException
	 */
	public List<AuthInspectionCollect> queryAuthInspectionCollect(String major) throws SQLException{
		String sql="SELECT "
			+" s_content AS content,"
			+" s_name AS name,"
			+" s_school_name as schoolName,"
			+" s_expert_name AS expertName,"
			+" s_key AS keys,"
			+" s_value AS val,"
			+" s_mode AS mode,"
			+" i.s_major AS major"
			+" FROM"
			+" t_auth_inspection_opinions AS t"
			+" LEFT JOIN t_auth_expert as e on t.s_expert_id=e.p_id"
			+" LEFT JOIN t_auth_group as p on t.s_group_id=p.p_id"
			+" LEFT JOIN t_auth_item AS i on t.s_item_id=i.p_id"
			+" LEFT JOIN t_auth_school c on c.p_id=p.s_school_id"
			+" WHERE"
			+" i.s_major = ?" ;
		List<AuthInspectionCollect> list=dbHelper.getBeanList(sql, AuthInspectionCollect.class,major);
		return list;
	}
}
